package com.zlyx.easy.pio.model;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.zlyx.easy.core.buffer.EasyBuffer;
import com.zlyx.easy.core.loggers.Logger;
import com.zlyx.easy.core.utils.StringUtils;
import com.zlyx.easy.pio.exception.PioException;
import com.zlyx.easy.pio.util.PioUtil;

/**
 * 
 * @Auth 赵光
 * @Describle
 * @2019年1月3日 下午2:54:41
 */
public class PioModel {

	/**
	 * 原始文件路径
	 */
	private String filePath;

	/**
	 * 工作簿
	 */
	private Workbook workbook;

	/**
	 * 表格
	 */
	private Sheet sheet;

	/**
	 * 表格坐标
	 */
	private int sheetAt;

	/**
	 * 表格标题
	 */
	private String title;

	/**
	 * 表头名称
	 */
	private List<String> headers;

	/**
	 * 字段名称
	 */
	private List<String> fields;

	/**
	 * 
	 * @param filePath 路径
	 * @throws Exception
	 */
	public PioModel(String filePath) throws Exception {
		this(filePath, 0);
	}

	/**
	 * 
	 * @param filePath 路径
	 * @param title    标题
	 * @throws Exception
	 */
	public PioModel(String filePath, String title) throws Exception {
		this(filePath, 0, title);
	}

	/**
	 * @param filePath 路径
	 * @param sheetAt  坐标
	 * @throws Exception
	 */
	public PioModel(String filePath, int sheetAt) throws Exception {
		this(filePath, 0, null);
	}

	/**
	 * 
	 * @param filePath 路径
	 * @param sheetAt  坐标
	 * @param title    标题
	 * @throws Exception
	 */
	public PioModel(String filePath, int sheetAt, String title) throws Exception {
		this.filePath = filePath;
		this.sheetAt = sheetAt;
		this.title = title;
		this.workbook = PioUtil.getExcel(filePath);
		this.sheet = PioUtil.getSheet(workbook, sheetAt, title);
		this.headers = PioUtil.getHeaders(sheet);
		this.fields = new ArrayList<>();
	}

	/**
	 * @return the workbook
	 */
	public Workbook getWorkbook() {
		return workbook;
	}

	/**
	 * @return the sheet
	 */
	public Sheet getSheet() {
		return sheet;
	}

	/**
	 * 添加表头列
	 * 
	 * @param headers
	 * @return
	 * @throws Exception
	 */
	public PioModel setHeaders(String... headers) throws Exception {
		for (String header : headers) {
			if (!this.headers.contains(header)) {
				this.headers.add(header);
			}
		}
		return setHeaders(this.headers);
	}

	/**
	 * 设置表头列
	 * 
	 * @param headers
	 * @return
	 * @throws Exception
	 */
	public PioModel setHeaders(Collection<String> headers) throws Exception {
		this.headers = new ArrayList<>(headers);
		insertRow(headers);
		return this;
	}

	/**
	 * 添加表头映射关系
	 * 
	 * @param headers
	 * @return
	 * @throws Exception
	 */
	public PioModel setFields(String... fields) throws Exception {
		for (String field : fields) {
			if (!this.fields.contains(field)) {
				this.fields.add(field);
			}
		}
		return setFields(this.fields);
	}

	/**
	 * 设置表头映射关系
	 * 
	 * @param headers
	 * @return
	 * @throws Exception
	 */
	public PioModel setFields(Collection<String> fields) throws Exception {
		this.fields = new ArrayList<>(fields);
		return this;
	}

	/**
	 * 设置表头映射关系
	 * 
	 * @param headersMap 为了保持有序性，需要参数是LinkedHashMap的实体对象
	 * @return
	 * @throws Exception
	 */
	public PioModel setHeaders(Map<String, String> headersMap) throws Exception {
		if (headersMap != null) {
			setFields(headersMap.keySet());
			setHeaders(headersMap.values());
		}
		return this;
	}

	/**
	 * 将excel转化为map数据
	 * 
	 * @return
	 */
	public List<Map<String, String>> toMap() {
		if (fields.isEmpty()) {
			Logger.warn("没有配置字段映射规则!");
			fields = headers;
		}
		if (fields.size() < headers.size()) {
			Logger.warn("字段映射规则少字段，即将不完整匹配!");
		}
		List<Map<String, String>> dataList = new ArrayList<>();
		Iterator<Row> rows = sheet.rowIterator();
		Row row = null;
		String cellValue = null;
		while (rows.hasNext()) {
			row = rows.next();
			Map<String, String> map = new LinkedHashMap<>();
			for (int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
				cellValue = row.getCell(cellnum) == null ? "" : row.getCell(cellnum).getStringCellValue();
				if (fields.size() > cellnum) {
					map.put(fields.get(cellnum), cellValue);
				}
			}
			dataList.add(map);
		}
		if (!headers.isEmpty()) {
			dataList.remove(0);
		}
		return dataList;
	}

	/**
	 * 追加数据到最后一行
	 * 
	 * @param values 行数据
	 * @throws Exception
	 */
	public void appendRow(Collection<String> values) throws Exception {
		insertRow(values, sheet.getLastRowNum() + 1);
	}

	/**
	 * 插入数据到第一行
	 * 
	 * @param values 行数据
	 * @throws Exception
	 */
	public void insertRow(Collection<String> values) throws Exception {
		insertRow(values, 0);
	}

	/**
	 * 插入一行数据
	 * 
	 * @param values 行数据
	 * @param rowNum 插入行
	 * @throws Exception
	 */
	public void insertRow(Collection<String> values, int rowNum) throws Exception {
		insertRow(values, rowNum, 0);
	}

	/**
	 * 插入一行数据
	 * 
	 * @param values   行数据
	 * @param rowNum   插入行
	 * @param startNum 偏移量
	 * @throws Exception
	 */
	public void insertRow(Collection<String> values, int rowNum, int offset) throws Exception {
		PioUtil.insertRow(workbook, sheetAt, title, new ArrayList<>(values), rowNum, offset);
	}

	/**
	 * 插入一行数据
	 * 
	 * @param values 行数据
	 * @throws Exception
	 */
	public void insertColumn(Collection<String> values) throws Exception {
		insertColumn(values, 0);
	}

	/**
	 * 插入一行数据
	 * 
	 * @param values    行数据
	 * @param columnNum 插入行
	 * @throws Exception
	 */
	public void insertColumn(Collection<String> values, int columnNum) throws Exception {
		insertColumn(values, 0, 0);
	}

	/**
	 * 插入一行数据
	 * 
	 * @param values 行数据
	 * @param rowNum 插入行
	 * @param offset 偏移量
	 * @throws Exception
	 */
	public void insertColumn(Collection<String> values, int columnNum, int offset) throws Exception {
		PioUtil.insertColumn(workbook, sheetAt, title, new ArrayList<>(values), columnNum, offset);
	}

	/**
	 * 将map对象转换为表格数据
	 * 
	 * @param data
	 * @throws Exception
	 */
	public void transer(Map<String, String> data) throws Exception {
		if (this.headers.isEmpty()) {
			setHeaders(data.keySet());
		}
		insertRow(data.values(), 1);
	}

	/**
	 * 将集合数据转换为表格数据
	 * 
	 * @param datas
	 * @throws Exception
	 */
	public void transer(List<Map<String, String>> datas) throws Exception {
		if (datas == null || datas.isEmpty()) {
			PioException.throwException("不支持转换空数据!");
		}
		if (this.headers.isEmpty()) {
			setHeaders(datas.get(0).keySet());
		}
		for (int i = 0; i < datas.size(); i++) {
			insertRow(datas.get(i).values(), i + 1);
		}
	}

	/**
	 * 写出到excel源文件
	 * 
	 * @throws Exception
	 * 
	 */
	public void write() throws Exception {
		write(filePath);
	}

	/**
	 * 写出到指定excel文件
	 * 
	 * @param filePath 写出路径
	 * @throws Exception
	 */
	public void write(String filePath) throws Exception {
		view();
		PioUtil.writeExcel(workbook, filePath);
	}

	/**
	 * 预览试图
	 */
	public void view() {
		System.out.println(toString());
	}

	@Override
	public String toString() {
		EasyBuffer eb = EasyBuffer.newBuffer();
		Iterator<Row> rows = sheet.rowIterator();
		Row row = null;
		String cellValue = null;
		while (rows.hasNext()) {
			row = rows.next();
			for (int cellnum = 0; cellnum < row.getLastCellNum(); cellnum++) {
				cellValue = row.getCell(cellnum) == null ? "" : row.getCell(cellnum).getStringCellValue();
				eb.append("|").append(StringUtils.enough(cellValue + "", 10));
			}
			eb.append("|\n");
		}
		return eb.toString();
	}

}
